MasterCashflow2
1. Description
Master Cashflow 2 cashflow type generates cashflows for each record based on the mapping provided for component,cashflow_amount,due_date,outstanding_amount,maturity_date.
2. Screen Configuration
Click ⬇️ to download the test-bed.
3. Cashflow Derivation Logic
Case 1: If 'Is Adjustment Required' is mapped as true,
IS_ADJUSTMENT_REQUIRED: true
ACCOUNT_ID|OUTSTANDING_AMOUNT|INTEREST_AMOUNT|PRINCIPAL_AMOUNT|DUE_DATE|MATURITY_DATE
ACC1001|5000.00|500.00|2000.00|31-01-2024|31-05-2024
ACC1001|5000.00|50.00|500.00|29-02-2024|31-05-2024
ACC1001|5000.00|50.00|500.00|31-03-2024|31-05-2024
ACC1001|5000.00|50.00|500.00|30-04-2024|31-05-2024
ACC1001|5000.00|50.00|500.00|31-05-2024|31-05-2024
for generation of cashflows, the principal amount will get stamped in principal amount field and interest amount will get stamped in interest amount field.
For the account 'ACC1001', the cashflows generated will be
principal_amount|interest_amount|cashflow_date
2000.00|500.00|31-01-2024
500.00|50.00|29-02-2024
500.00|50.00|31-03-2024
500.00|50.00|30-04-2024
500.00|50.00|31-05-2024
1000.00|0.00|31-05-2024 --> adjusted cashflow
Here the last cashflow will adjusted amount,
logic to derive the adjustment amount:
total principal amount = 2000.00+500.00+500.00+500.00+500.00 = 4000.00
adjustment_amount = outstanding_amount - total_principal_amount
= 5000.00 - 4000.00
= 1000.00
Case 2: If 'Is Adjustment Required' is mapped as false,
ACCOUNT_ID|OUTSTANDING_AMOUNT|INTEREST_AMOUNT|PRINCIPAL_AMOUNT|DUE_DATE|MATURITY_DATE
ACC1001|5000.00|500.00|2000.00|31-01-2024|31-05-2024
ACC1001|5000.00|50.00|500.00|29-02-2024|31-05-2024
ACC1001|5000.00|50.00|500.00|31-03-2024|31-05-2024
ACC1001|5000.00|50.00|500.00|30-04-2024|31-05-2024
ACC1001|5000.00|50.00|500.00|31-05-2024|31-05-2024
for generation of cashflows, the principal amount will get stamped in principal amount field and interest amount will get stamped in interest amount field.
For the account 'ACC1001', the cashflows generated will be
principal_amount|interest_amount|cashflow_date
2000.00|500.00|31-01-2024
500.00|50.00|29-02-2024
500.00|50.00|31-03-2024
500.00|50.00|30-04-2024
500.00|50.00|31-05-2024
4. Required Fields
| # | Parameters | Description | Is_Mandatory_Field | Possible_Values |
|---|---|---|---|---|
| 1 | cashflow_account_id | The field which needs to be considered as the key to store the cashflows. | YES | - |
| 2 | principal_amount | The amount field which needs to be stamped as principal amount. | YES | - |
| 3 | interest_amount | The amount field which needs to be stamped as interest amount. | YES | - |
| 4 | cashflow_date | The date field which needs to stamped as cashflow date. | YES | - |
| 5 | master_account_id | The field which needs to be stamped as account id, and to be used to do lookup on cashflow file | YES | - |
| 6 | master_outstanding_amount | The total outstanding amount of the account. | YES | - |
| 7 | master_maturity_date | The date field which needs to be considered as account end date. | YES | - |
| 8 | is_adjustment_required | The flag which will decided if adjustment cashflow is required or not, if total principal amount is not equal to outstanding amount. | NO | true or false |
5. Working Excel
Click ⬇️ to download the excel calculation.